﻿using EasyERP_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyERP_DAL.Base
{
    /// <summary>
    /// 往来单位数据访问类
    /// </summary>
    public class B_Partner_DAL
    {
        #region 获取往来单位信息表
        /// <summary>
        /// 获取往来单位信息表
        /// </summary>
        /// <param name="model">往来单位信息实体对象</param>
        /// <returns>往来单位信息表</returns>
        public DataTable Get_B_Partner(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                       SELECT bp_cd,
                       bp_full_nm,
                       bp_nm,
                       bp_addr,
                       bp_reper,
                       bp_tel,
                       bp_email,
                       bp_tax,
                       so_flag,
                       po_flag,
                       bank_cd,
	                   dbo.Get_Minor_nm(N'1002',bank_cd) AS bank_cd_nm,
                       bank_acct_no,
                       pay_type,
	                   dbo.Get_Minor_nm(N'1001',pay_type)AS pay_type_nm,
                       remark
                FROM dbo.b_partner
                WHERE CASE WHEN N'{model.so_flag}' = N'S' THEN so_flag WHEN N'{model.so_flag}' = N'P' THEN po_flag ELSE N'Y' END = N'Y'
                AND bp_full_nm LIKE N'%{model.bp_full_nm}%'
                AND	bp_reper LIKE N'%{model.bp_reper}%';");

            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }
        #endregion

        #region 添加往来单位信息
        /// <summary>
        /// 添加往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：添加成功；返回False：添加失败</returns>
        public bool Insert_B_Partner(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                INSERT INTO dbo.b_partner
                (
	                bp_cd,bp_full_nm,bp_nm,bp_addr,bp_reper,bp_tel,bp_email,bp_tax,so_flag,
	                po_flag,bank_cd,bank_acct_no,pay_type,remark,ins_user,ins_date,up_user,up_date
                )
                VALUES
                (
	                N'{model.bp_cd}', N'{model.bp_full_nm}', N'{model.bp_nm}', N'{model.bp_addr}', N'{model.bp_reper}',
                    N'{model.bp_tel}', N'{model.bp_email}', N'{model.bp_tax}',N'{model.so_flag}', N'{model.po_flag}',
                    N'{model.bank_cd}', N'{model.bank_acct_no}', N'{model.pay_type}', N'{model.remark}', N'{model.user_cd}', GETDATE(), N'{model.user_cd}', GETDATE()
                );
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 删除往来单位信息
        /// <summary>
        /// 删除往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：删除成功；返回False：删除失败</returns>
        public bool Delete_B_Partner(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                DELETE FROM dbo.b_partner WHERE bp_cd = N'{model.bp_cd}';
            ");
            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 修改往来单位信息
        /// <summary>
        /// 修改往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：修改成功；返回False：修改失败</returns>
        public bool Update_B_Partner(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                UPDATE dbo.b_partner
                SET bp_full_nm = N'{model.bp_full_nm}',
                    bp_nm = N'{model.bp_nm}',
                    bp_addr = N'{model.bp_addr}',
                    bp_reper = N'{model.bp_reper}',
                    bp_tel = N'{model.bp_tel}',
                    bp_email = N'{model.bp_email}',
                    bp_tax = N'{model.bp_tax}',
                    so_flag = N'{model.so_flag}',
                    po_flag = N'{model.po_flag}',
                    bank_cd = N'{model.bank_cd}',
                    bank_acct_no = N'{model.bank_acct_no}',
                    pay_type = N'{model.pay_type}',
                    remark = N'{model.remark}',
                    up_user = N'{model.user_cd}',
                    up_date = GETDATE()
                WHERE bp_cd = N'{model.bp_cd}';
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 查询数据是否存在
        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists(B_Partner model)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1 FROM dbo.b_partner WHERE bp_cd = N'{model.bp_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion

        #region 判断采购订单中是否存在登记该往来单位信息
        /// <summary>
        /// 判断采购订单中是否存在登记该往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists_Po_dtl(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.po_hdr
                WHERE bp_cd = N'{model.bp_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        } 
        #endregion

        #region 判断销售订单中是否存在登记该往来单位信息
        /// <summary>
        /// 判断销售订单中是否存在登记该往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists_So_dtl(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.so_hdr
                WHERE bp_cd = N'{model.bp_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        } 
        #endregion

        #region 判断商品单价中是否存在登记该往来单位信息
        /// <summary>
        /// 判断商品单价中是否存在登记该往来单位信息
        /// </summary>
        /// <param name="model">往来信息单位实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists_B_Item_Price(B_Partner model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.b_item_price
                WHERE bp_cd = N'{model.bp_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        } 
        #endregion
    }
}
